/**
 * @Copyright: Interactive Business Solution Pte Ltd, Singapore
 * @Author: Li Wen, 2007-11-18
 * @Version: 071224.002
 * Database initialization class, this class is used for first use of Tourix, creates tables if table
 * does not exist.
 * Tables are needed to create: Package, Photo, Price, City, Weather, Itinerary, Departure, TourItem,
 * Marker, Customer, User, CityMap, Hotel, Currency, Flight (changed from FlightItem), System
 */
package com.ibsol.database
{
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	
	public class DBInit
	{
		private var _tourixDBConn:SQLConnection;
		/**
		 * Gets Tourix database connection, default constructor of class DBInit
		 * Creates all tables do not exist in tourix.db
		 * @param: null
		 * @return: void
		 */
		public function DBInit()
		{
			_tourixDBConn = DBConnManager.getConnection("tourix");
			createPackageTable();
			createPhotoTable();
			createPriceTable();
			createCityTable();
			createWeatherTable();
			createItinerayTable();
			createDepartureTable();
			createTourItemTable();
			createMarkerTable();
			createCustomerTable();
			createUserTable();
			createCityMapTable();
			createHotelTable();
			createCurrencyTable();
			createFlightTable();
			createSystemTable();
		}
		/**
		 * Create table package (id, name, duration, price, isProm, thumbPic, xml, category, offlineOperator, updateTime)
		 * if table package does not exist
		 * @param: null
		 * @return: void
		 */
		private function createPackageTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS package (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					"name VARCHAR(200) default null, duration INTEGER default null, price NUMERIC default null," + 
					"isProm INTEGER default null, thumbPic VARCHAR(200) default null, xml TEXT default null," + 
					"category VARCHAR(50) default null, offlineOperator VARCHAR(10) default null," + 
					"updateTime VARCHAR(25) default null);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * Create table photo (id, fid, type, name, desp, sequence, offlineOperator, updateTime) if table photo does not exist
		 * @param: null
		 * @return: void
		 */
		private function createPhotoTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS photo (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					"fid INTEGER default null, type VARCHAR(20) default null, name VARCHAR(200) default null," + 
					"desp VARCHAR(200) default null, sequence INTEGER default null, offlineOperator VARCHAR(10) default null," + 
					"updateTime VARCHAR(25) default null);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * Create table price (id, packageid, type, P1A, P2A, P3A, P1A1C, P2A1C, P2A2C, tax, offlineOperator, updateTime)
		 * if table price does not exist
		 * @param: null
		 * @return: void
		 */
		private function createPriceTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS price (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					"packageid INTEGER default null, type VARCHAR(20) default null, P1A NUMERIC default null," + 
					"P2A NUMERIC default null, P3A NUMERIC default null, P1A1C NUMERIC default null," + 
					"P2A1C NUMERIC default null, P2A2C NUMERIC default null, tax NUMERIC default null," +
					"offlineOperator VARCHAR(20) default null, updateTime VARCHAR(25) default null);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * Create table city (id, name, cityCode, country, continent, offlineOperator, updateTime)
		 * if table city does not exist
		 * @param: null
		 * @return: void
		 */
		private function createCityTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS city (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					"name VARCHAR(50) default null, cityCode VARCHAR(50) default null, country VARCHAR(50) default null," + 
					"continent VARCHAR(50) default null, offlineOperator VARCHAR(20) default null, updateTime VARCHAR(25) default null," + 
					"x INTEGER default null, y INTEGER default null);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * Create table weather (id, cityid, cityName, cityCode, degree, forecast, offlineOperator, updateTime)
		 * if table weather does not exist
		 * @param: null
		 * @return: void
		 */
		private function createWeatherTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS weather (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					"cityid INTEGER default null, cityName VARCHAR(50) default null, cityCode VARCHAR(50) default null," + 
					"celcius VARCHAR(20) default null, forecast TEXT default null,offlineOperator VARCHAR(20) default null," + 
					"updateTime VARCHAR(25) default null, high VARCHAR(10) default null, low VARCHAR(10) default null," +
					"humidity VARCHAR(20) default null, visibility VARCHAR(20) default null, des VARCHAR(20) default null);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * Create table itinerary (id, name, packageid, desp, breakfast, lunch, meal, offlineOperator, updateTime)
		 * if table itinerary does not exist
		 * @param: null
		 * @return: void
		 */
		private function createItinerayTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS itinerary (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					"name VARCHAR(50) default null, packageid INTEGER default null, desp TEXT default null," + 
					"breakfast VARCHAR(200) default null, lunch VARCHAR(200) default null, meal VARCHAR(200) default null," + 
					"offlineOperator VARCHAR(10) default null, updateTime VARCHAR(25) default null, sequence INTEGER default 0);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * Create table departure (id, packageid, departureDate, departureMonth, slot, available, offlineOperator, updateTime)
		 * if table departure does not exist
		 * @param: null
		 * @return: void
		 */
		private function createDepartureTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS departure (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					"packageid INTEGER default null, departureDate INTEGER default null, departureMonth INTEGER default null, departureYear INTEGER default null," + 
					"slot INTEGER default null, available INTEGER default null, offlineOperator VARCHAR(10) default null," + 
					"updateTime VARCHAR(25) default null);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * Create table tourItem (id, packageid, itineraryid, name, cityid, type, desp, offlineOperator, updateTime, sequence)
		 * if table touritem does not exist
		 * @param: null
		 * @return: void
		 */
		private function createTourItemTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS tourItem (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					"packageid INTEGER default null, itineraryid INTEGER default null, name VARCHAR(200) default null," + 
					"cityid INTEGER default null, type VARCHAR(10) default null, desp TEXT default null," + 
					"offlineOperator VARCHAR(10) default null, updateTime VARCHAR(25) default null, sequence INTEGER default 0);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * Creates table tourItemLib (id, name, cityid, type, desp, offlineOperator, updateTime)
		 * if table tourItemLib does not exist
		 * @param:	null
		 * @return:	void
		 */
		private function createTourItemLibTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS tourItem (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					"name VARCHAR(200) default null, cityid INTEGER default null, type VARCHAR(20) default null," + 
					"desp TEXT default null,offlineOperator VARCHAR(10) default null, updateTime VARCHAR(25) default null);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * Create table marker (id, cityMapid, packageid, cityid, x, y, title, desp, offlineOperator, updateTime)
		 * if table marker does not exist
		 * @param: null
		 * @return: void
		 */
		private function createMarkerTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS marker (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					"cityMapid INTEGER default null, packageid INTEGER default null, cityid INTEGER default null," + 
					"x INTEGER default null, y INTEGER default null, title VARCHAR(200) default null," + 
					"desp TEXT default null, offlineOperator VARCHAR(20) default null, updateTime VARCHAR(25) default null);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * Create table customer (id, uid, name, mobile, homeNo, email, gender, isConfirm, comments, offlineOperator, updateTime)
		 * if table customer does not exist
		 * @param: null
		 * @return: void
		 */
		private function createCustomerTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS customer (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					"uid INTEGER default null, name VARCHAR(100) default null, mobile VARCHAR(50) default null," + 
					"homeNo VARCHAR(50) default null, email VARCHAR(200) default null, gender VARCHAR(1) default null," + 
					"isConfirm INTEGER default null, comments TEXT default null, offlineOperator VARCHAR(20) default null," + 
					"updateTime VARCHAR(25) default null);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * Create table user (id, acc, pwd, level, photo, offlineOperator, updateTime) if table user does not exist
		 * @param: null
		 * @return: void
		 */
		private function createUserTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
				"acc VARCHAR(50) default null, pwd VARCHAR(200) default null, level INTEGER default null," + 
				"photo VARCHAR(200) default null, offlineOperator VARCHAR(20) default null, updateTime VARCHAR(25) default null);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * Create table cityMap (id, packageid, name, scale, initX, initY, offlineOperator, updateTime)
		 * if table cityMap does not exist
		 * @param: null
		 * @return: void
		 */
		private function createCityMapTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS cityMap (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					"packageid INTEGER default null, name VARCHAR(200) default null, scale VARCHAR(20) default null," + 
					"initX NUMERIC default null, initY NUMERIC default null, offlineOperator VARCHAR(20) default null," + 
					"updateTime VARCHAR(25) default null);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * Create table hotel (id, cityid, rating, desp, offlineOperator, updateTime) if table hotel does not exist
		 * @param: null
		 * @return: void
		 */
		private function createHotelTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS hotel (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					"cityid INTEGER default null, rating INTEGER default null, desp TEXT default null," + 
					"offlineOperator VARCHAR(20) default null, updateTime VARCHAR(25) default null);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * Create table currency (id, fromMoney, toMoney, rate, offlineOperator, updateTime) if table currency does not exist
		 * @param: null
		 * @return: void
		 */
		private function createCurrencyTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS currency (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					"changeFrom VARCHAR(20) default null, changeTo VARCHAR(20) default null, exchangeRate NUMERIC default null," + 
					"offlineOperator VARCHAR(20) default null, updateTime VARCHAR(25) default null);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * Changed the flightItem to flight and added aircraft column -> Dolita
		 * Create table flight Table (id, packageid, airline, aircraft, flightNo, departureDate, arrivalDate, duration,
		 * start, destination, logo, economy, business, first, offlineOperator, updateTime) if table flight does not exist
		 * @param: null
		 * @return: void
		 */
		private function createFlightTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS flight (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					"packageid INTEGER default null, airline VARCHAR(50) default null, aircraft VARCHAR(20) default null," + 
					"flightNo VARCHAR(20) default null, departureDate VARCHAR(200) default null, arrivalDate VARCHAR(200) default null," + 
					"duration VARCHAR(50) default null, start VARCHAR(50) default null, destination VARCHAR(50) default null," + 
					"logo VARCHAR(50) default null, economy NUMERIC default null, business NUMERIC default null, first NUMERIC default null," + 
					"offlineOperator VARCHAR(20) default null, updateTime VARCHAR(20) default null);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * Create table system (id, serverAddr, userAcc, pwd, level, salesmanPhoto, updateTime) if table system does not exist
		 * @param: null
		 * @return: void
		 */
		private function createSystemTable():void
		{
			var sqlStr:String = "CREATE TABLE IF NOT EXISTS system (id INTEGER PRIMARY KEY AUTOINCREMENT," + 
					"serverAddr VARCHAR(50) default null, userAcc VARCHAR(50) default null, pwd VARCHAR(200) default null," + 
					"level INTEGER default null, salesmanPhoto VARCHAR(100) default null, updateTime VARCHAR(50) default null);";
			var createSQL:SQLStatement = new SQLStatement();
			createSQL.sqlConnection = _tourixDBConn;
			createSQL.addEventListener(SQLErrorEvent.ERROR, statusHandler);
			createSQL.text = sqlStr;
			createSQL.execute();
		}
		/**
		 * The function will be called when there is SQLErrorEvent happens, debug mode function
		 * @param: event:SQLErrorEvent event handler pass in parameter
		 * @return: void
		 */
		private function statusHandler(event:SQLErrorEvent):void
		{
			trace(event.text);
			return;
		}
	}
}